package com.maszy.service;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.maszy.db.DBConnection;

public class RptService {
	private static PreparedStatement pst;
	private static ResultSet rs = null;
	//销售汇总查询
	public List queryRpt(Map m) {
		List list = new ArrayList();
		DBConnection con = new DBConnection();
		String sql = "";
		if(m.size()>0){
			String sss=(String) m.get("doorStore_Id");
			//按门店查询所有收银机收款记录。
			if(sss!=null&&!"".equals(sss)){
				sql="select trtotal.* from trtotal where trtotal.ecrid in (select Maszy_CashRegister.Maszy_CashRegister_NO from Maszy_CashRegister,Maszy_DoorStore where Maszy_DoorStore.Maszy_DoorStore_Id=? and Maszy_DoorStore.Maszy_DoorStore_Id=Maszy_CashRegister.Maszy_DoorStore_Id) and trtotal.dsale between ? and ?  and trtotal.addrate!=0 order by trtotal.dsale asc";
				System.out.println(sql);
				try {
					pst = con.getConnection().prepareStatement(sql);
					pst.setString(1,(String) m.get("doorStore_Id") );
					
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}else{
				//按收银机号查询该收银机收款记录。
				sql="select trtotal.* from trtotal where trtotal.ecrid in (select Maszy_CashRegister.Maszy_CashRegister_NO from Maszy_CashRegister where Maszy_CashRegister.Maszy_CashRegister_id=?) and trtotal.dsale between ? and ? and trtotal.addrate!=0 order by trtotal.dsale asc";
				try {				
					pst = con.getConnection().prepareStatement(sql);				
					pst.setString(1,(String) m.get("cashRegister_Id") );
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			
		}
		try {			
			String time=(String) m.get("time");
			pst.setString(2, time+" 00:00:00");
			pst.setString(3, time+" 23:59:59");
		
			rs = pst.executeQuery();
			while (rs.next()) {
				Map<String, Object> map = new HashMap<String, Object>();
				ResultSetMetaData rsmd = rs.getMetaData();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					map.put(rsmd.getColumnName(i), rs.getObject(i));
				}
				list.add(map);
			}

		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return list;
	}
	public Double queryRptSum(Map m) {		
		DBConnection con = new DBConnection();
		Double d = null;
		String sql = "";
		if(m.size()>0){
			String sss=(String) m.get("doorStore_Id");
			//按门店查询所有收银机收款总数。
			if(sss!=null&&!"".equals(sss)){
				sql="select SUM(AmtAdd) from trtotal where trtotal.ecrid in (select Maszy_CashRegister.Maszy_CashRegister_NO from Maszy_CashRegister,Maszy_DoorStore where Maszy_DoorStore.Maszy_DoorStore_Id=? and Maszy_DoorStore.Maszy_DoorStore_Id=Maszy_CashRegister.Maszy_DoorStore_Id) and trtotal.dsale between ? and ? and trtotal.addrate!=0";
				try {
					pst = con.getConnection().prepareStatement(sql);
					pst.setString(1,(String) m.get("doorStore_Id") );
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			
			}else{
				//按收银机号查询该收银机收款总数。
				sql="select SUM(AmtAdd) from trtotal where trtotal.ecrid in (select Maszy_CashRegister.Maszy_CashRegister_NO from Maszy_CashRegister where Maszy_CashRegister.Maszy_CashRegister_id=?) and trtotal.dsale between ? and ? and trtotal.addrate!=0";
				try {				
					
					pst = con.getConnection().prepareStatement(sql);				
					pst.setString(1,(String) m.get("cashRegister_Id") );
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			
		}
		
		try {			
			pst.setString(2, (String)m.get("time")+" 00:00:00");
			pst.setString(3, (String)m.get("time")+" 23:59:59");
			rs = pst.executeQuery();
			while (rs.next()) {
				d=rs.getDouble(1);
			}

		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return d;
	}
	//销售单笔流水具体点餐列表查询
	public List queryRptBySaleNO(String id) {
		List list = new ArrayList();
		DBConnection con = new DBConnection();
//		String sql = "select TrItem.* from TrItem where TrItem.SaleNo=?";	
		String sql="select TrItem.*,[AIS20101130140857].[dbo].[t_ICItem].fName from TrItem,[AIS20101130140857].[dbo].[t_ICItem] where TrItem.SaleNo=? and  [AIS20101130140857].[dbo].[t_ICItem].fNumber=tritem.bcode";
			try {			
			pst = con.getConnection().prepareStatement(sql);
			pst.setString(1,id);
			rs = pst.executeQuery();
			while (rs.next()) {
				Map<String, Object> map = new HashMap<String, Object>();
				ResultSetMetaData rsmd = rs.getMetaData();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					map.put(rsmd.getColumnName(i), rs.getObject(i));
				}
				
				list.add(map);
			}

		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return list;
	}
	//销售单笔流水付款方式查询
	public List queryRptPayBySaleNO(String id) {
		List list = new ArrayList();
		DBConnection con = new DBConnection();
		String sql = "select TrPay.* from TrPay where TrPay.SaleNo=? order by TrPay.ItemNo";	
		try {			
			pst = con.getConnection().prepareStatement(sql);
			pst.setString(1,id);
			rs = pst.executeQuery();
			while (rs.next()) {
				Map<String, Object> map = new HashMap<String, Object>();
				ResultSetMetaData rsmd = rs.getMetaData();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					if("PayName".equals(rsmd.getColumnName(i))){
						if("找收现".equals(rs.getObject(i).toString())){
							map.put(rsmd.getColumnName(i), "找零");
						}
						else{
							map.put(rsmd.getColumnName(i), rs.getObject(i));
						}
					}
					else{
						map.put(rsmd.getColumnName(i), rs.getObject(i));
					}
					
				}
				list.add(map);
			}

		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return list;
	}
}
